#!/usr/bin/env python3
# -*- encoding: utf-8 -*-
# author: wll
# create date: 2024-04-15 19:53:51

import pymysql
from pymysql.cursors import DictCursor
from loguru import logger


class MySQLHandler(object):
    """
    数据库操作类
    """

    def __init__(self, host=None, port=3306, user=None, password=None, database=None, charset=None, connect_timeout=60, autocommit=False, **kwargs):
        self.host = host or kwargs.get("host")
        self.port = port or kwargs.get("port")
        self.user = user or kwargs.get("user")
        self.password = password or kwargs.get("password")
        self.database = database or kwargs.get("database")
        self.charset = charset or kwargs.get("charset")
        self.connect_timeout = connect_timeout or kwargs.get("connect_timeout")
        self.autocommit = autocommit or kwargs.get("autocommit")

        self.conn = None
        self.cursor = None

    def _connect(self):
        """
        数据库链接
        """
        try:
            self.conn = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset=self.charset,
                connect_timeout=self.connect_timeout,
                autocommit=self.autocommit,
            )
            self.cursor = self.conn.cursor(DictCursor)
        except Exception as e:
            logger.error(e)

    def query_one_record(self, sql_text: str) -> tuple:
        """
        查询一条数据记录
        :return:
        :author: king
        :create date time: 2024/4/15 下午9:50
        """
        try:
            self.cursor.execute(sql_text)
            result = self.cursor.fetchone()
            return True, result
        except Exception as e:
            logger.error(f"{sql_text} 查询失败。\n{e}")
            return False, e

    def query_all_records(self, sql_text: str) -> tuple:
        """
        查询所有的记录
        :return:
        :author: king
        :create date time: 2024/4/15 下午9:54
        """
        try:
            self.cursor.execute(sql_text)
            results = self.cursor.fetchall()
            return True, results
        except Exception as e:
            logger.error(f"{sql_text} 查询失败。\n{e}")
            return False, e

    def query_many_records(self, sql_text: str, many_number: int = 1000) -> tuple:
        """
        查询指定数量的数据记录
        :return:
        :author: king
        :create date time: 2024/4/15 下午10:00
        """
        try:
            self.cursor.execute(sql_text)
            results = self.cursor.fetchmany(many_number)
            return True, results
        except Exception as e:
            logger.error(f"{sql_text} 查询失败。\n{e}")
            return False, e

    def create_data_table(self, sql_text: str):
        """
        :return:
        :author: king
        :create date time: 2024/4/15 下午10:11
        """
        try:
            self.cursor.execute(sql_text)
            self.conn.commit()
            return True, ""
        except Exception as e:
            self.conn.rollback()
            logger.error(f"{sql_text} 创建数据表失败\n{e}")
            return False, e

    def insert_into_data_table(self, table_name: str, field_name_list_by_table: list, data_list):
        """
        插入数据
        :param table_name: 数据表名称
        :param field_name_list_by_table: 数据表字段名称列表
        :param data_list: 数据列表，元素是元组。一个元组对应一条数据
        :return: tuple(bool, str)
        :author: king
        :create date time: 2024/4/15 下午10:14
        """
        field_name_string = ",".join(field_name_list_by_table)
        place_holder = ["?"] * len(field_name_list_by_table)

        sql_text = f"""
                        INSERT INTO ({field_name_string}) {table_name}
                        VALUES
                        ({','.join(place_holder)})
                    """
        try:
            self.cursor.executemany(sql_text, data_list)
            self.conn.commit()
            logger.debug(sql_text)
            return True, ""
        except Exception as e:
            self.conn.rollback()
            logger.error(f"{sql_text} 数据写入失败\n{e}")
            return False, e

    def update_data_table(self, update_sql_text: str):
        """
        更新数据
        :param update_sql_text: 更新语句
        :return:
        :author: king
        :create date time: 2024/4/15 下午10:26
        """
        try:
            self.cursor.execute(update_sql_text)
            self.conn.commit()
            return True, ""
        except Exception as e:
            self.conn.rollback()
            logger.error(f"{update_sql_text} 更新数据失败\n{e}")
            return False, e

    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()

    def __enter__(self):
        self._connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if all([exc_type, exc_val, exc_tb]):
            logger.error("{} {} {}", exc_type, exc_val, exc_tb)
        self.close()


def main():
    """
    :return:
    :author: king
    :create date time: 2024/4/15 下午10:04
    """
    keyword = {
        "host": "127.0.0.1",
        "port": 3306,
        "user": "root",
        "password": "wang3180",
        "database": "world",
        "charset": "utf8",
    }
    with MySQLHandler(**keyword) as sql_obj:
        results = sql_obj.query_many_records("select * from city;", 100)
        logger.info(results)

        results = sql_obj.query_all_records("select * from city;")
        logger.info(results)

        results = sql_obj.query_one_record("select * from city;")
        logger.info(results)
        # table_name = "wanglele"
        # field_name_list_by_table = ["name", "sex", "age"]
        # data_list = [("wang", "male", "0"), ("guo", "female", "20")]
        # sql_obj.insert_into_data_table(table_name, field_name_list_by_table, data_list)


if __name__ == "__main__":
    main()
